package persistence.impl;

import domain.Account;
import domain.Category;
import persistence.AccountDAO;
import persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDAOImpl implements AccountDAO {
    private static final String getAccountByUsername = "SELECT SIGNON.USERNAME, ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,\n" +
            "ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,\n" +
            "ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "WHERE ACCOUNT.USERID = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String getAccountByUsernameAndPassword = "SELECT SIGNON.USERNAME, ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,\n" +
            "ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,\n" +
            "ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "WHERE ACCOUNT.USERID = ? AND SIGNON.PASSWORD = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String insertAccount = "INSERT INTO ACCOUNT\n" +
            "(USERID, EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE)\n" +
            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String insertProfile = "INSERT INTO PROFILE (USERID, LANGPREF, FAVCATEGORY)\n" +
            "VALUES (?,?,?)";

    private static final String insertSignOn = "INSERT INTO SIGNON (USERNAME,PASSWORD)\n" +
            "VALUES (?,?)";

    private static final String updateAccount = "UPDATE ACCOUNT SET EMAIL = ?,FIRSTNAME = ?,LASTNAME = ?,\n" +
            "STATUS = ?,ADDR1 = ?,ADDR2 = ?,CITY = ?,STATE = ?,ZIP = ?,COUNTRY = ?,PHONE = ?\n" +
            "WHERE USERID = ?";

    private static final String updateProfile = "UPDATE PROFILE SET LANGPREF = ?,\n" +
            "FAVCATEGORY = ?\n" +
            "WHERE USERID = ?";

    private static final String updateSignOn = "UPDATE SIGNON SET PASSWORD = ?\n" +
            "WHERE USERNAME = ?";



    @Override
    public Account getAccountByUsername(String username) {
        Account account = null;
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(getAccountByUsername);
            statement.setString(1,username);
            ResultSet resultSet = statement.executeQuery();
            account = new Account();
            while(resultSet.next())
            {
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setState(resultSet.getString(9));
                account.setZip(resultSet.getString(10));
                account.setCountry(resultSet.getString(11));
                account.setPhone(resultSet.getString(12));
                account.setLanguagePreference(resultSet.getString(13));
                account.setFavouriteCategoryId(resultSet.getString(14));
                account.setListOption(resultSet.getInt(15) == 1);
                account.setBannerOption(resultSet.getInt(16) == 1);

                account.setBannerName(resultSet.getString(17));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }

        return account;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account1) {
        Account account = null;
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(getAccountByUsernameAndPassword);
            statement.setString(1,account1.getUsername());
            statement.setString(2,account1.getPassword());
            ResultSet resultSet = statement.executeQuery();
            account = new Account();
            while(resultSet.next())
            {
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setState(resultSet.getString(9));
                account.setZip(resultSet.getString(10));
                account.setCountry(resultSet.getString(11));
                account.setPhone(resultSet.getString(12));
                account.setLanguagePreference(resultSet.getString(13));
                account.setFavouriteCategoryId(resultSet.getString(14));
                account.setListOption(resultSet.getInt(15) == 1);
                account.setBannerOption(resultSet.getInt(16) == 1);

                account.setBannerName(resultSet.getString(17));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }

        return account;
    }

    @Override
    public void insertAccount(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(insertAccount);
            statement.setString(1,account.getUsername());
            statement.setString(2,account.getEmail());
            statement.setString(3,account.getFirstName());
            statement.setString(4,account.getLastName());
            statement.setString(5,account.getStatus());
            statement.setString(6,account.getAddress1());
            statement.setString(7,account.getAddress2());
            statement.setString(8,account.getCity());
            statement.setString(9,account.getState());
            statement.setString(10,account.getZip());
            statement.setString(11,account.getCountry());
            statement.setString(12,account.getPhone());

            statement.execute();

            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertProfile(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(insertProfile);
            statement.setString(1,account.getUsername());
            statement.setString(2,account.getLanguagePreference());
            statement.setString(3,account.getFavouriteCategoryId());

            statement.execute();

            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(insertSignOn);
            statement.setString(1,account.getUsername());
            statement.setString(2,account.getPassword());

            statement.execute();

            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(updateAccount);

            statement.setString(1,account.getEmail());
            statement.setString(2,account.getFirstName());
            statement.setString(3,account.getLastName());
            statement.setString(4,account.getStatus());
            statement.setString(5,account.getAddress1());
            statement.setString(6,account.getAddress2());
            statement.setString(7,account.getCity());
            statement.setString(8,account.getState());
            statement.setString(9,account.getZip());
            statement.setString(10,account.getCountry());
            statement.setString(11,account.getPhone());
            statement.setString(12,account.getUsername());

            statement.executeUpdate();

            System.out.println("update Account");
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(updateProfile);
            statement.setString(1,account.getLanguagePreference());
            statement.setString(2,account.getFavouriteCategoryId());
            statement.setString(3,account.getUsername());

            statement.executeUpdate();

            System.out.println("update Profile");
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try{
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(updateSignOn);
            statement.setString(1,account.getPassword());
            statement.setString(2,account.getUsername());

            statement.executeUpdate();

            System.out.println("update Password");
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}
